package com.util;

import com.bean.*;

import java.util.*;

/**
 * @author Smile
 */
public class EvidenceMaterialServer {
    /**
     * 添加电子卷宗目录
     *
     * @param catalogs
     * @return
     */
    public static List<SQLiteArgument> addDossierCatalog(List<DossierCatalog> catalogs) {
        List<SQLiteArgument> arguments = new ArrayList<>();
        HashMap<Integer, Object> parameter = null;
        for (DossierCatalog item : catalogs) {
            String sql = "REPLACE INTO yx_dzjz_jzml (JZBH,MLBH,SFSC,DWBM,BMSAH,FMLBH,MLXX,MLSXH,MLBM,MLLX,SFSM,SSLBBM,SSLBMC,MLXSMC,NodeID,NodeParentID) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            parameter = new LinkedHashMap<>();
            parameter.put(1, item.getJZBH());
            parameter.put(2, item.getMLBH());
            parameter.put(3, item.getSFSC());
            parameter.put(4, item.getDWBM());
            parameter.put(5, item.getBMSAH());
            parameter.put(6, item.getFMLBH());
            parameter.put(7, item.getMLXX());
            parameter.put(8, item.getMLSXH());
            parameter.put(9, item.getMLBM());
            parameter.put(10, item.getMLLX());
            parameter.put(11, item.getSFSM());
            parameter.put(12, item.getSSLBBM());
            parameter.put(13, item.getSSLBMC());
            parameter.put(14, item.getMLXSMC());
            parameter.put(15, item.getNodeNo());
            parameter.put(16, item.getNodeParentNo());
            arguments.add(new SQLiteArgument(sql, parameter));
        }
        return arguments;
    }

    public static List<SQLiteArgument> addDocDossierCatalog(List<DocDossierCatalog> catalogs) {
        List<SQLiteArgument> arguments = new ArrayList<>();
        HashMap<Integer, Object> parameter;
        for (DocDossierCatalog item : catalogs) {
            String sql = "REPLACE INTO yx_ws_ajjzml (BMSAH,TYSAH,SFSC,FQDWBM,FQL,ZHXGSJ,DWBM,JZMLH,JZMLMC,JZMLBM,JZFMLH,JZMLLX,SFLXSC,SCPH,NodeID,NodeParentID)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            parameter = new LinkedHashMap<>();
            parameter.put(1, item.getBMSAH());
            parameter.put(2, item.getTYSAH());
            parameter.put(3, item.getSFSC());
            parameter.put(4, item.getFQDWBM());
            parameter.put(5, item.getFQL());
            parameter.put(6, item.getZHXGSJ());
            parameter.put(7, item.getDWBM());
            parameter.put(8, item.getJZMLH());
            parameter.put(9, item.getJZMLMC());
            parameter.put(10, item.getJZMLBM());
            parameter.put(11, item.getJZFMLH());
            parameter.put(12, item.getJZMLLX());
            parameter.put(13, item.getSFLXSC());
            parameter.put(14, item.getSCPH());
            parameter.put(15, item.getNodeNo());
            parameter.put(16, item.getNodeParentNo());
            arguments.add(new SQLiteArgument(sql, parameter));
        }
        return arguments;
    }

    /**
     * 生成电子卷宗分表
     *
     * @param tableName
     * @return
     */
    public static SQLiteArgument generateTable(String tableName) {
        String sql = String.format("CREATE TABLE if not exists %s (WJXH VARCHAR,ID VARCHAR PRIMARY KEY,IMG CLOB,[ORDER] NUMERIC)", tableName);
        return new SQLiteArgument(sql);
    }


    /**
     * 添加电子卷宗分表记录
     *
     * @param tableName
     * @param fileCatalog
     * @return
     */
    public static SQLiteArgument addTableRelation(String tableName, DossierFileCatalog fileCatalog) {
        String sql = "insert into yx_dzjz_tpb select ?,?,? where not exists (select wjxh from yx_dzjz_tpb where wjxh=?)";

        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, fileCatalog.getWJXH());
        parameter.put(2, tableName);
        parameter.put(3, fileCatalog.getMLBH());
        parameter.put(4, fileCatalog.getWJXH());
        return new SQLiteArgument(sql, parameter);
    }


    /**
     * 添加电子卷宗文件
     *
     * @param dossier
     * @return
     */
    public static List<SQLiteArgument> addDossierFile(DossierFileCatalog dossier) {
        String sql = null;
        Map<Integer, Object> parameter = null;
        List<SQLiteArgument> arguments = new ArrayList<>();

        if (StringUtils.isEmpty(dossier.getNodeNo())) {
            sql = "UPDATE yx_dzjz_jzmlwj SET pdf = ?,text =? WHERE WJXH =?";
            parameter = new LinkedHashMap<>();

            parameter.put(1, dossier.getPDF());
            parameter.put(2, dossier.getTEXT());
            parameter.put(3, dossier.getWJXH());
            arguments.add(new SQLiteArgument(sql, parameter));

            if (!StringUtils.isEmpty(dossier.getWJBM()) && dossier.getIMG() != null) {
                for (Map.Entry<Integer, String> item : dossier.getIMG().entrySet()) {
                    sql = String.format("INSERT INTO %s (WJXH,ID,IMG,[ORDER])VALUES (?,?,?,?)", dossier.getWJBM());
                    parameter = new LinkedHashMap<>();
                    parameter.put(1, dossier.getWJXH());
                    parameter.put(2, UUID.randomUUID().toString());
                    parameter.put(3, item.getValue());
                    parameter.put(4, item.getKey());
                    arguments.add(new SQLiteArgument(sql, parameter));
                }
            }
        } else {
            // 添加卷宗PDF文件
            sql = "INSERT INTO yx_dzjz_jzmlwj (JZBH,MLBH,WJXH,SFSC,DWBM,BMSAH,WJLJ,WJMC,WJXSMC,WJHZ,WJKSY,WJJSY,WJBZXX,WJYZBZ,WJSXH,WJZDY,SSLBBM,SSLBMC,PDF,TEXT,NodeID,NodeParentID)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            parameter = new LinkedHashMap<>();

            parameter.put(1, dossier.getJZBH());
            parameter.put(2, dossier.getMLBH());
            parameter.put(3, dossier.getWJXH());
            parameter.put(4, dossier.getSFSC());
            parameter.put(5, dossier.getDWBM());
            parameter.put(6, dossier.getBMSAH());
            parameter.put(7, dossier.getWJLJ());
            parameter.put(8, dossier.getWJMC());
            parameter.put(9, dossier.getWJXSMC());
            parameter.put(10, dossier.getWJHZ());
            parameter.put(11, dossier.getWJKSY());
            parameter.put(12, dossier.getWJJSY());
            parameter.put(13, dossier.getWJBZXX());
            parameter.put(14, dossier.getWJYZBZ());
            parameter.put(15, dossier.getWJSXH());
            parameter.put(16, dossier.getWJZDY());
            parameter.put(17, dossier.getSSLBBM());
            parameter.put(18, dossier.getSSLBMC());
            parameter.put(19, dossier.getPDF());
            parameter.put(20, dossier.getTEXT());
            parameter.put(21, dossier.getNodeNo());
            parameter.put(22, dossier.getNodeParentNo());
            arguments.add(new SQLiteArgument(sql, parameter));
        }
//
//        sql = String.format("DELETE FROM %s WHERE WJXH=?", dossier.getWJBM());
//        parameter = new LinkedHashMap<>();
//        parameter.put(1, dossier.getWJXH());
//        arguments.add(new SQLiteArgument(sql, parameter));

        return arguments;
    }

    /**
     * 添加文书卷宗文件
     *
     * @param docdossier
     * @return
     */
//    public static List<SQLiteArgument> addDocDossierFile(DocDossierFileCatalog docdossier, Map<Integer, String> imgs) {
    public static List<SQLiteArgument> addDocDossierFile(DocDossierFileCatalog docdossier) {
        String sql = null;
        Map<Integer, Object> parameter = null;
        List<SQLiteArgument> arguments = new ArrayList<>();

        if (StringUtils.isEmpty(docdossier.getNodeNo())) {
            sql = "UPDATE yx_ws_ajjzwj SET pdf = ?,text=? where wsbh = ?";
            parameter = new LinkedHashMap<>();
            parameter.put(1, docdossier.getPDF());
            parameter.put(2, docdossier.getTEXT());
            parameter.put(3, docdossier.getWSBH());
            arguments.add(new SQLiteArgument(sql, parameter));
            if (docdossier.getIMG() != null) {
                for (Map.Entry<Integer, String> item : docdossier.getIMG().entrySet()) {
                    sql = "INSERT INTO yx_ws_ajjztpl (WSBH,ID,IMG,[ORDER])VALUES (?,?,?,?)";
                    parameter = new LinkedHashMap<>();
                    parameter.put(1, docdossier.getWSBH());
                    parameter.put(2, UUID.randomUUID().toString());
                    parameter.put(3, item.getValue());
                    parameter.put(4, item.getKey());
                    arguments.add(new SQLiteArgument(sql, parameter));
                }
            }
        } else {
            sql = "INSERT INTO yx_ws_ajjzwj (BMSAH,TYSAH,SFSC,FQDWBM,FQL,CJSJ,ZHXGSJ,DWBM,JZMLH,JZWJH,WSBH,XGRQ,WJMC,WJLX,WSCFLJ,FWSBH,NZRGH,NZRXM,WJKZM,RJSJ,RJRGH,RJRXM,WJSX,GXYWBMS,SFKYGX,WSMBBH,YZBH,YZMC,YYZT,DYZT,YYHWJBH,YJDYFS,BYZDYFS,SFJH,WH,SFLXSC,SCPH,PDF,NodeID,NodeParentID,TEXT)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            parameter = new LinkedHashMap<>();
            parameter.put(1, docdossier.getBMSAH());
            parameter.put(2, docdossier.getTYSAH());
            parameter.put(3, docdossier.getSFSC());
            parameter.put(4, docdossier.getFQDWBM());
            parameter.put(5, docdossier.getFQL());
            parameter.put(6, docdossier.getCJSJ());
            parameter.put(7, docdossier.getZHXGSJ());
            parameter.put(8, docdossier.getDWBM());
            parameter.put(9, docdossier.getJZMLH());
            parameter.put(10, null);
            parameter.put(11, docdossier.getWSBH());
            parameter.put(12, docdossier.getXGRQ());
            parameter.put(13, docdossier.getWJMC());
            parameter.put(14, docdossier.getWJLX());
            parameter.put(15, docdossier.getWSCFLJ());
            parameter.put(16, docdossier.getFWSBH());
            parameter.put(17, docdossier.getNZRGH());
            parameter.put(18, docdossier.getNZRXM());
            parameter.put(19, docdossier.getWJKZM());
            parameter.put(20, docdossier.getRJSJ());
            parameter.put(21, docdossier.getRJRGH());
            parameter.put(22, docdossier.getRJRXM());
            parameter.put(23, docdossier.getWJSX());
            parameter.put(24, docdossier.getGXYWBMS());
            parameter.put(25, docdossier.getSFKYGX());
            parameter.put(26, docdossier.getWSMBBH());
            parameter.put(27, docdossier.getYZBH());
            parameter.put(28, docdossier.getYZMC());
            parameter.put(29, docdossier.getYYZT());
            parameter.put(30, docdossier.getDYZT());
            parameter.put(31, docdossier.getYYHWJBH());
            parameter.put(32, docdossier.getYJDYFS());
            parameter.put(33, docdossier.getBYZDYFS());
            parameter.put(34, null);
            parameter.put(35, docdossier.getWH());
            parameter.put(36, docdossier.getSFLXSC());
            parameter.put(37, docdossier.getSCPH());
            parameter.put(38, docdossier.getPDF());
            parameter.put(39, docdossier.getNodeNo());
            parameter.put(40, docdossier.getNodeParentNo());
            parameter.put(41, docdossier.getTEXT());
            arguments.add(new SQLiteArgument(sql, parameter));
        }

        return arguments;
    }

    /**
     * 添加登录用户信息
     *
     * @param argument
     * @return
     */
    public static SQLiteArgument addUserInfo(Map<String, Object> argument) {
        String sql = "INSERT INTO login_user (unitCode,unitName,userName,userPsd) VALUES(?,?,?,?)";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, argument.get("DWBM"));
        parameter.put(2, argument.get("DWMC"));
        parameter.put(3, argument.get("MC"));
        parameter.put(4, argument.get("KL").toString().toUpperCase());

        return new SQLiteArgument(sql, parameter);
    }

    /**
     * 添加案件基本信息
     *
     * @param caseInfo
     * @return
     */
    public static SQLiteArgument addCaseInfo(Map<String, Object> caseInfo) {
        String sql = "INSERT INTO tyyw_gg_ajjbxx (BMSAH,TYSAH,AJMC,CBDW_BM,CBDW_MC,AJLB_BM,AJLB_MC,AJZT,YJZT,CBRGH,CBR,SLRQ,BLTS,DQRQ,BJRQ,WCRQ,GDRQ,CBBM_BM,CBBM_MC,YSAY_AYDM,YSAY_AYMC,YSDW_DWDM,YSDW_DWMC) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, caseInfo.get("bmsah"));
        parameter.put(2, caseInfo.get("tysah"));
        parameter.put(3, caseInfo.get("ajmc"));
        parameter.put(4, caseInfo.get("cbdwbm"));
        parameter.put(5, caseInfo.get("cbdwmc"));
        parameter.put(6, caseInfo.get("ajlbbm"));
        parameter.put(7, caseInfo.get("ajlbmc"));
        parameter.put(8, caseInfo.get("ajzt"));
        parameter.put(9, caseInfo.get("yjzt"));
        parameter.put(10, caseInfo.get("cbjcgbm"));
        parameter.put(11, caseInfo.get("cbjcg"));
        parameter.put(12, caseInfo.get("slrq"));
        parameter.put(13, caseInfo.get("blts"));
        parameter.put(14, caseInfo.get("dqrq"));
        parameter.put(15, caseInfo.get("bjrq"));
        parameter.put(16, caseInfo.get("wcrq"));
        parameter.put(17, caseInfo.get("gdrq"));
        parameter.put(18, caseInfo.get("cbbmbm"));
        parameter.put(19, caseInfo.get("cbbmmc"));
        parameter.put(20, caseInfo.get("ysaydm"));
        parameter.put(21, caseInfo.get("ysaymc"));
        parameter.put(22, caseInfo.get("ysdwdm"));
        parameter.put(23, caseInfo.get("ysdwmc"));

        return new SQLiteArgument(sql, parameter);
    }

    /**
     * 添加涉案人信息
     *
     * @param bmsah
     * @param tysah
     * @param argument
     * @return
     */
    public static List<SQLiteArgument> addPerpetrator(String bmsah, String tysah, List<Map<String, Object>> argument) {
        List<SQLiteArgument> arguments = new ArrayList<>();
        String sql = "INSERT INTO tyyw_gg_xyrjbxx (BMSAH,TYSAH,XYRBH,XM,CYM,CH,XB_DM,XB_MC,CSRQ,ZJLX_DM,ZJLX_MC,ZJHM,MZ_DM,MZ_MC,GJ_DM,GJ_MC,HJSZD_DM,HJSZD_MC) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        for (Map<String, Object> item : argument) {
            Map<Integer, Object> parameter = new LinkedHashMap<>();
            parameter.put(1, bmsah);
            parameter.put(2, tysah);
            parameter.put(3, item.get("zrrbm"));

            parameter.put(4, item.get("xm"));
            parameter.put(5, item.get("cym"));
            parameter.put(6, item.get("ch"));
            parameter.put(7, item.get("xbdm"));
            parameter.put(8, item.get("xbmc"));
            parameter.put(9, item.get("csrq"));
            parameter.put(10, item.get("zjlxdm"));
            parameter.put(11, item.get("zjlxmc"));
            parameter.put(12, item.get("zjhm"));
            parameter.put(13, item.get("mzdm"));
            parameter.put(14, item.get("mzmc"));
            parameter.put(15, item.get("gjdm"));
            parameter.put(16, item.get("gjmc"));
            parameter.put(17, item.get("hjszddm"));
            parameter.put(18, item.get("hjszdmc"));
            arguments.add(new SQLiteArgument(sql, parameter));
        }

        return arguments;
    }


    /**
     * 获取需要下载证据材料
     *
     * @param codes
     * @return
     */
    public static SQLiteArgument getDownloadMaterial(List<String> codes) {
        String sql = null;
        if (codes == null || codes.size() == 0) {
            sql = "select BMSAH,WJMC,DWBM,a.WJXH,WJXSMC,WJHZ,'1' WJLX,b.WJBM from yx_dzjz_jzmlwj a INNER JOIN yx_dzjz_tpb b on a.wjxh =b.WJXH where pdf ='' or pdf is null union all select BMSAH, WJMC, DWBM, WSBH, WJMC, WJKZM,'2','' from yx_ws_ajjzwj where pdf = '' or pdf is null";
        } else {
            String codeStr = String.join(",", codes);
            sql = String.format("select BMSAH,WJMC,DWBM,a.WJXH,WJXSMC,WJHZ,'1' WJLX,b.WJBM from yx_dzjz_jzmlwj a INNER JOIN yx_dzjz_tpb b on a.wjxh =b.WJXH where a.wjxh in (%s) and (pdf ='' or pdf is null) union all select BMSAH, WJMC, DWBM, WSBH, WJMC, WJKZM,'2','' from yx_ws_ajjzwj where wsbh in (%s) and (pdf = '' or pdf is null)", codeStr, codeStr);
        }
        return new SQLiteArgument(sql);
    }


    /**
     * 获取电子卷宗PDF
     *
     * @param fileID
     * @return
     */
    public static SQLiteArgument getDossierPdf(String fileID) {
        String sql = "select BMSAH,WJMC,DWBM,a.WJXH,WJXSMC,WJHZ,PDF,wjbm from  yx_dzjz_jzmlwj a left join yx_dzjz_tpb b on a.wjxh = b.wjxh where a.wjxh=?";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, fileID);
        return new SQLiteArgument(sql, parameter);
    }

    /**
     * 获取文书卷宗PDF
     *
     * @param fileID
     * @return
     */
    public static SQLiteArgument getDocDossierPdf(String fileID) {
        String sql = "select WSBH,WJMC,WJKZM,PDF from yx_ws_ajjzwj where wsbh=?";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, fileID);
        return new SQLiteArgument(sql, parameter);
    }

    /**
     * 添加附件文件信息
     *
     * @param pdfInfo
     * @param imgInfos
     * @return
     */
    public static List<SQLiteArgument> addAttachment(Map<String, String> pdfInfo, List<Map<String, String>> imgInfos) {
        List<SQLiteArgument> parameters = new ArrayList<>();
        String sql = "INSERT INTO yx_fj_wj (ID,WJL,WJMC,WJHZ,TEXT,SCSJ)VALUES (?,?,?,?,?,datetime('now'))";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, pdfInfo.get("ID"));
        parameter.put(2, pdfInfo.get("WJL"));
        parameter.put(3, pdfInfo.get("WJMC"));
        parameter.put(4, pdfInfo.get("WJHZ"));
        parameter.put(5, pdfInfo.get("TEXT"));
        parameters.add(new SQLiteArgument(sql, parameter));

        if (imgInfos != null) {
            for (Map<String, String> item : imgInfos) {
                sql = "INSERT INTO yx_fj_tpl (WJID,ID,IMG,[ORDER])VALUES (?,?,?,?)";
                parameter = new LinkedHashMap<>();
                parameter.put(1, item.get("WJID"));
                parameter.put(2, item.get("ID"));
                parameter.put(3, item.get("IMG"));
                parameter.put(4, item.get("ORDER"));
                parameters.add(new SQLiteArgument(sql, parameter));
            }
        }
        return parameters;
    }

    /**
     * 添加示证目录
     *
     * @param dataSource
     * @return
     */
    public static SQLiteArgument addEvidenceCatalog(String dataSource) {
        List<SQLiteArgument> parameters = new ArrayList<>();
        String sql = "REPLACE INTO yx_ya_szml(id,data)VALUES (1,?)";
        Map<Integer, Object> parameter = new LinkedHashMap<>();
        parameter.put(1, dataSource);
        return new SQLiteArgument(sql, parameter);
    }
}
